MovieNet.com is a new e-business that will specialize in video on demand – essentially renting movies via download. They want you to develop a prototype that demonstrates potential functionality (using Microsoft Access – not web technology – that’s in the advanced DB class). We are to develop an integrated database application that will keep track of movies, and info about them that might be of interest to a customer, customers, and critics ratings of the movies. In this system, we are not interested in calculating MovieNet’s revenues. You will need to enter sufficient data to demonstrate the capabilities of your prototype.
Functionality provided should include:
· User friendly interface(s) for data input for each kind of data
· User friendly interfaces for update/delete for any kinds of data subject to frequent updates
· Menus leading users to the capabilities that they can make use of
· Support for some pre-planned reports and queries. An initial specification of desirable queries/ reports is given below. This list is tentative and subject to change.
We will talk more about this project in upcoming weeks. Your first task is to hand in a survey to aid me in assigning people to compatible groups. This should be done ASAP (today!). Note that the DB design is due Mar 14, a first installment is due Apr 4, and the completed project is due Apr 27. The will be a handout later on project deliverables.
NOTE: Some (NOT ALL) business rules include (even some obvious rules are left out):
You will probably have to ask me about other business rules as you get into design. Some of the queries are tricky too. Don’t wait until the last minute to get going.
Tentative Queries / Reports:
1. Show all Comedies, with Year (released in theatres), Title, MPAA Rating, and Runtime (in minutes), ordered by Year Released (descending) and Title.
2. Show all Movies that won “Best Picture” Oscars, with Year, Title, Genre, Director, Leading Actor, and Leading Actress, ordered by Year. (Report strongly suggested)
3. Show all movies whose country of origin is NOT the USA, with their Title, Director, Year, Country, Language, whether there are subtitles, ordered by Director and Year.
4. Show all Movies available on video starting this week (within 7 days of today), with Title, Genre, MPAA Rating, Director, Leading Actor, and Leading Actress. (Report strongly suggested) Hint: today’s date can be obtained using now().
5. Show all movies with Actress X as leading actress, with Year, Title, Genre, Role (Character name), and whether they were nominated and whether they received an Oscar for the performance, ordered by Year.
6. Show all Movies viewed by Customer X (may be identified by e-mail address instead of name), with Customer Assigned Rating (0-10), Title, Year, Genre, and Date Viewed, ordered by Rating Assigned (descending) and Title.
7. Show all Movies released in Year X, with Title, Director, Genre, MPAA Rating and Box Office Revenues (in millions of dollars), ordered by Box Office Revenues (descending).
8. Show all Movies described by Keyword X, with Year, Title, Genre, MPAA Rating, and Runtime ordered by Year (descending) and Title.
9. Show all Movies with Director X, with Year, Title, Genre, MPAA Rating, and Runtime, ordered by Year and Title.
10. Show all Movies that somebody might like if they liked Movie X, with Year, Title, Genre, MPAA Rating, Leading Actor, and Leading Actress, ordered by Year, and Title (based on info entered by MovieNet staff) . (Report strongly suggested)
11. Show all Movies in Genre X, Released since Year Y, with Year, MPAA Rating, Title, ordered by MPAA Rating and Title.
12. Show all Customers who have viewed Movie X in the last Y days, with their E-Mail Address, Date Viewed, Country of Residence, Gender, Year of Birth, and Customer Assigned Rating, ordered by Country, then Gender, then Year of Birth.
13. Show all Movies that will be available on video in the next X days with Genre Y, with their Title, Director, MPAA Rating, Release Year (in theatres), Available Date (on video), Leading Actor, and Leading Actress, ordered by Available Date. (Report strongly suggested)
14. Show all Actors, Actresses, and Directors whose birthday is today (think about this one – not that they were born today). Hint: today’s date can be obtained using now().
15. Show the average numeric rating assigned to movies by Critic, with Critic Name, Average Rating, and number of ratings, ordered by average rating (descending).
16. Show the average customer assigned rating assigned by Movie, with Movie Title, Average Rating, and number of ratings, ordered by average rating (descending)
17. Show the average customer assigned rating by Movie in Genre X, with Movie Title and Average Rating, ordered by average rating (descending)
18. Show the average customer assigned rating by Genre, with Genre and Average Rating, ordered by average rating (descending)
19. Show for all actors, the number of times they have been leading actor in movies, with actor Name, Date of Birth, and number of times, ordered by number of times (descending).
20. Show the average number of movies viewed by our customers (one average across customers – a little tricky)
21. Show the total Box Office Revenues earned in by all Movies (combined) from Year X in genre Y and the number of movies in the Year/Genre combination (result should be one row).
NOTES:
· Always remember when designing output that these are designed to help humans. Thus, for example, if the primary key value is not meaningful to humans, other identifying info should be displayed in addition/instead. E.g. In addition to or instead of movie ID, display movie title.
· Many of these should be “parameterized” in order to be flexible. Some, but not necessarily all, of the queries that should be parameterized are indicated with X, Y, Z etc.
· If the description includes the word “total”, I’m looking for some summarization, not just a list of raw data. The “by” clause is an attempt to indicate what results are to be summarized “by”.
· It probably makes sense to organize your application by what kinds of users need what capabilities. E.g. Detailed info about movies (director, stars, box office revenues) will be entered by MovieNet staff, while customers’ ratings of movies will be entered by customers. If you have any questions about who might do what (such as who might be interested in different queries), please ask.
· All of the above will be queries. Do at least 5 as reports based on their query. Reports are particularly valuable if subtotals or totals or grouping of data is of interest in addition to details. When info about a movie is desired including leading actors and actresses reports are very valuable because they can be grouped under the movie instead of creating extra rows with other movie info repeated.
· Somehow (at least in documentation) let me know which query is which (by number)
· These are tentative. If you find any of these that are problematic, check with me. Also, if you think of any great additional canned queries/reports, feel free to include them.